150

Beginner’s Guide to Code Algorithms

150

STEP 4

Initialize variables and file paths, get the data from the website, load the

US, maps and populate the data sheet in the format of the “Any State” sheet

mentioned above.

Public Declare PtrSafe Function GetKeyboardState Lib “user32” (pbKeyState As

Byte) As Long

Public Declare PtrSafe Function GetAsyncKeyState Lib “User32.dll” (ByVal vKey As

Long) As Long

Public States() As String

Public FirstTime As String

Public CurrentWorkbookName As String

Sub CovidGraph()

  Dim NewBook As Workbook

  Dim filepath As String

  Dim startrow(500) As Integer

  Dim rRange As Range

  Dim fileopenWb As Workbook, thisWb As Workbook

  Dim fileopenMapWb As Workbook

  Dim fileopenWs As Worksheet, thisWs As Worksheet

  Workbooks(CurrentWorkbookName).Activate

  ‘MacroWorkBookName =​ ThisWorkbook.FullName

  Set thisWb =​ ActiveWorkbook

  Set thisWs =​ thisWb.Sheets(“Any State”)

  Set thisWsGraphs =​ thisWb.Sheets(“Graphs”)

  If UserForm1.OptionButton1 =​ True Then

    ConfirmedOrDeaths =​ “C”

    thisWsGraphs.Cells(23, 3) =​ “Cases”

  Else

    ConfirmedOrDeaths =​ “D”

    thisWsGraphs.Cells(23, 3) =​ “Deaths”

  End If

    FileToValidate1 =​ “covid_​confirmed_​usafacts.csv”

    FileToValidate2 =​ “covid_​deaths_​usafacts.csv”

 

  If ConfirmedOrDeaths =​ “C” Then

    SheetToValidate =​ “covid_​confirmed_​usafacts”

    FileToValidate =​ “covid_​confirmed_​usafacts.csv”

  Else

    SheetToValidate =​ “covid_​deaths_​usafacts”

    FileToValidate =​ “covid_​deaths_​usafacts.csv”

  End If

  filepath =​ ActiveWorkbook.Path & “\” & FileToValidate

  filepath1 =​ ActiveWorkbook.Path & “\” & FileToValidate1

  filepath2 =​ ActiveWorkbook.Path & “\” & FileToValidate2

  filepathMap =​ ActiveWorkbook.Path & “\” & “USAMapCovid.xlsx”